USE [texchange_db]
GO

/****** Object:  StoredProcedure [dbo].[SP_GetAllCompanyFavCount]    Script Date: 05/29/2011 19:39:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[SP_GetAllCompanyFavCount] 	
	@lang bigint,
	@start  bigint=0,
	@limit bigint=30,
	@order nvarchar(10) = 'id',
	@orderdir nvarchar(4) = 'desc',
	@cid bigint
	-- Add the parameters for the function here 
AS
BEGIN 
	Declare @SQLQuery AS NVarchar(4000)
	SET @SQLQuery = 'SELECT count(distinct(id)) as count FROM 
			(SELECT DISTINCT(Company.id), ROW_NUMBER() OVER (ORDER BY Company.id asc) AS rownum FROM Company 
			LEFT JOIN Places ON Company.country_id = Places.id '
	IF @lang = 1
	BEGIN
		SET @SQLQuery = @SQLQuery + ' LEFT JOIN UniversalType ON Company.type_company_id = UniversalType.id '
	END
	ELSE
	BEGIN
		SET @SQLQuery = @SQLQuery + ' LEFT JOIN UniversalType ON (Company.type_company_id = UniversalType.parent_id AND UniversalType.language_id=@lang) '
	END
	SET @SQLQuery = @SQLQuery + ' INNER JOIN CJCompanyTransport ON Company.id=CJCompanyTransport.company_id WHERE 1=1'
	IF @cid > 1
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Company.id IN (SELECT favorite_id FROM Favorites WHERE company_id = @cid) '
	END
	SET @SQLQuery = @SQLQuery + ') as a WHERE rownum >=@start AND rownum<=@limit'
	Declare @ParamDefinition AS NVarchar(2000) 
	Set @ParamDefinition = ' @lang bigint,
			@start  bigint=0,
			@limit bigint=30,
			@order nvarchar(10),
			@orderdir nvarchar(4),
			@cid bigint'
	--PRINT @SQLQuery
	--INSERT INTO @Company
	Execute sp_Executesql @SQLQuery, @ParamDefinition,@lang,@start,@limit,@order,@orderdir,@cid
	RETURN
END


GO


